package com.wy.wuye.db;

import com.wy.wuye.javabean.User;
import com.wy.wuye.utils.AutoTool;
import com.wy.wuye.utils.NestTools;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.List;
import java.util.Locale;

import static java.lang.System.currentTimeMillis;

/**
 * 数据库基本语言
 */
public class DBLg {

    /**
     * 查询用户名和密码
     */
    public static User getUserForPhone(String phone, String pwd, JdbcTemplate jdbcTemplate) {
        String sql = "SELECT phone,pwd,token FROM user WHERE phone=? and pwd=?;";
        try {
            List<User> list = jdbcTemplate.query(sql, new Object[]{phone, pwd}, new BeanPropertyRowMapper<User>(User.class));
            return list != null && list.size() > 0 ? list.get(0) : null;
        } catch (EmptyResultDataAccessException d) {
            return null;
        }
    }


    /**
     * 查询用户名和密码
     */
    public static User getUserForName(String name, String pwd, JdbcTemplate jdbcTemplate) {
        String sql = "SELECT name,pwd FROM user WHERE phone=? and pwd=?;";
        try {
            List<User> list = jdbcTemplate.query(sql, new Object[]{name, pwd}, new BeanPropertyRowMapper<User>(User.class));
            return list != null && list.size() > 0 ? list.get(0) : null;
        } catch (EmptyResultDataAccessException d) {
            return null;
        }
    }

    /**
     * 使用token登录
     *
     * @param token
     * @param jdbcTemplate
     * @return
     */
    public static User getUserForToken(String token, JdbcTemplate jdbcTemplate) {
        String sql = "SELECT name,pwd,createtime FROM user WHERE token=?;";
        try {
            List<User> list = jdbcTemplate.query(sql, new Object[]{token}, new BeanPropertyRowMapper<User>(User.class));
            return list != null && list.size() > 0 ? list.get(0) : null;
        } catch (EmptyResultDataAccessException d) {
            return null;
        }
    }

    /**
     * 使用token登录
     *
     * @param token
     * @param jdbcTemplate
     * @return
     */
    public static User getUser(String token, JdbcTemplate jdbcTemplate) {
        String sql = "SELECT email,phone,createtime,picture,name FROM user WHERE token=?;";
        try {
            List<User> list = jdbcTemplate.query(sql, new Object[]{token}, new BeanPropertyRowMapper<User>(User.class));
            return list != null && list.size() > 0 ? list.get(0) : null;
        } catch (EmptyResultDataAccessException d) {
            return null;
        }
    }


    public static User insertUser(String pwd, String email, String phone, JdbcTemplate jdbcTemplate) {
        String name = phone;
        String token = NestTools.getToken(pwd, email, phone);
        String sql = "INSERT INTO user (name,pwd,email,phone,token,`createtime`) VALUES (?,?,?,?,?,?);";
        Date date = new Date(currentTimeMillis());
        int result = jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement) throws SQLException {
                preparedStatement.setString(1, name);
                preparedStatement.setString(2, pwd);
                preparedStatement.setString(3, email);
                preparedStatement.setString(4, phone);
                preparedStatement.setString(5, token);
                preparedStatement.setTimestamp(6, new Timestamp(System.currentTimeMillis()), Calendar.getInstance(Locale.CHINA));

            }
        });

        return result > 0 ? new User(name, pwd, email, phone, token, date.toString()) : null;

    }


    /**
     * 更新用户头像图片
     *
     * @param user
     * @param token
     * @param jdbcTemplate
     * @return
     */
    public static boolean updatePicture(User user, String token, JdbcTemplate jdbcTemplate) {

        String sql = "UPDATE user SET picture=? WHERE token=?;";
        int result = jdbcTemplate.update(sql, new Object[]{user.getPicture(),token});
        if (result > 0) {
            return true;
        }
        return false;
    }

    /**
     * 查询用户是否已经注册过了
     *
     * @param phone
     * @param jdbcTemplate
     * @return
     */
    public static User hasRegister(String phone, String email, JdbcTemplate jdbcTemplate) {
        String sql = "SELECT * FROM user WHERE phone=? OR email=?;";
        try {
            List<User> list = jdbcTemplate.query(sql, new Object[]{phone, email}, new BeanPropertyRowMapper<User>(User.class));
            if (list != null && list.size() > 0)
                return list.get(0);
            else
                return null;
        } catch (EmptyResultDataAccessException d) {
            return null;
        }
    }

}
